import pandas as pd
import sqlite3
from .tabledatabase import db_manager



# 连接到内存数据库

class sql_table:
    def __init__(self, config: dict,datapath:str) -> None:
        self.config=config
        self.datapath=datapath
        
        self.output_table_name = config["output_table"]
        self.output_table = []
        
        self.conn = sqlite3.connect(":memory:")
        
        self.input_tables=config["input_tables"]
        self.table_to_sql()
        
        
        self.sql:str = config["sql"]
        df = pd.read_sql_query(self.sql, self.conn)
        df.fillna("",inplace=True)
        with db_manager(self.datapath) as db:
            db[self.output_table_name]=df.to_dict('records')
        
        self.conn.close()
    
    def table_to_sql(self):
        with db_manager(self.datapath) as db:
            for tablename in self.input_tables:
                data= db.get(tablename,[])
                pd.DataFrame(data).to_sql(tablename, self.conn, if_exists='replace',index=False)